﻿-- ADMINISTER OBJECTS
CREATE PROCEDURE [todoc].[sspAdministerObjects]
(
@TableSchema SYSNAME,
@TableName SYSNAME
)
--WITH EXECUTE AS 'todocuser'
AS
/*
GENERATES/REGENERATES ALL THE TODOC OBJECTS FOR A GIVEN TABLE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
SET NOCOUNT ON;

DECLARE @HasView NVARCHAR(5) = 'False';
DECLARE @UsingShadowTable NVARCHAR(5) = 'False';
DECLARE @HasDeleteProc NVARCHAR(5) = 'False';
DECLARE @HasInsertProc NVARCHAR(5) = 'False';
DECLARE @HasUpdateProc NVARCHAR(5) = 'False';
DECLARE @HasSaveProc NVARCHAR(5) = 'False';
DECLARE @HasDeleteTrigger NVARCHAR(5) = 'False';
DECLARE @HasInsertTrigger NVARCHAR(5) = 'False';
DECLARE @HasUpdateTrigger NVARCHAR(5) = 'False';
DECLARE @UsingShadow BIT = 0;
DECLARE @ErrorSection NVARCHAR(20) = '';
DECLARE @TableColumns [todoc].[TableColumns];

BEGIN TRANSACTION;

BEGIN TRY

	INSERT INTO @TableColumns
	SELECT *
	FROM [todoc].[svTableColumns]
	WHERE TableSchema = @TableSchema
		AND TableName = @TableName;

	-- see what actions we need to take on this table
	SELECT	@HasView = MAX((CASE WHEN ep.[name] = 'Todoc: Has View' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@UsingShadowTable = MAX((CASE WHEN ep.[name] = 'Todoc: Has Shadow Table' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasDeleteProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Delete Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasInsertProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Insert Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasUpdateProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Update Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasSaveProc = MAX((CASE WHEN ep.[name] = 'Todoc: Has Save Procedure' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasDeleteTrigger = MAX((CASE WHEN ep.[name] = 'Todoc: Has Delete Trigger' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasInsertTrigger = MAX((CASE WHEN ep.[name] = 'Todoc: Has Insert Trigger' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END)),
			@HasUpdateTrigger = MAX((CASE WHEN ep.[name] = 'Todoc: Has Update Trigger' THEN CONVERT(NVARCHAR(5), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID(CONVERT(NVARCHAR(128), @TableSchema) + '.' + CONVERT(NVARCHAR(128), @TableName))
		AND ep.[name] LIKE 'Todoc: Has%';

	SET @ErrorSection = 'View';

	IF (@HasView = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropView]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		-- CREATE
		EXEC [todoc].[sspCreateView]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@TableColumnsCV = @TableColumns,
			@Debug = 0;

	END

	SET @ErrorSection = 'Shadow Table';

	IF (@UsingShadowTable = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropShadowTable]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		-- CREATE
		EXEC [todoc].[sspCreateShadowTable]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@TableColumnsCST = @TableColumns,
			@Debug = 0;

	END

	SET @ErrorSection = 'Delete SP';

	IF (@HasDeleteProc = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropDeleteSP]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
		
		IF (@UsingShadowTable = 'True' AND @HasDeleteTrigger = 'True')
		BEGIN
			-- has trigger so do not audit in the stored procedure
			SET @UsingShadow = 0;
		END
		ELSE
		BEGIN
			SET @UsingShadow = (CASE WHEN @UsingShadowTable = 'True' THEN 1 ELSE 0 END);
		END

		-- CREATE
		EXEC [todoc].[sspCreateDeleteSP]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCDSP = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;
	END

	SET @ErrorSection = 'Insert SP';

	IF (@HasInsertProc = 'True')
	BEGIN
		
		-- DROP
		EXEC [todoc].[sspDropInsertSP]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		IF (@UsingShadowTable = 'True' AND @HasInsertTrigger = 'True')
		BEGIN
			-- has trigger so do not audit in the stored procedure
			SET @UsingShadow = 0;
		END
		ELSE
		BEGIN
			SET @UsingShadow = (CASE WHEN @UsingShadowTable = 'True' THEN 1 ELSE 0 END);
		END
		
		-- CREATE
		EXEC [todoc].[sspCreateInsertSP]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCISP = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	SET @ErrorSection = 'Update SP';

	IF (@HasUpdateProc = 'True')
	BEGIN
		
		-- DROP
		EXEC [todoc].[sspDropUpdateSP]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		IF (@UsingShadowTable = 'True' AND @HasUpdateTrigger = 'True')
		BEGIN
			-- has trigger so do not audit in the stored procedure
			SET @UsingShadow = 0;
		END
		ELSE
		BEGIN
			SET @UsingShadow = (CASE WHEN @UsingShadowTable = 'True' THEN 1 ELSE 0 END);
		END 
		
		-- CREATE
		EXEC [todoc].[sspCreateUpdateSP]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCUSP = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	SET @ErrorSection = 'Save SP';

	IF (@HasSaveProc = 'True')
	BEGIN
		
		-- DROP
		EXEC [todoc].[sspDropSaveSP]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		IF (@UsingShadowTable = 'True' AND @HasInsertTrigger = 'True' AND @HasUpdateTrigger = 'True')
		BEGIN
			-- has triggers so do not audit in the stored procedure
			SET @UsingShadow = 0;
		END
		ELSE
		BEGIN
			SET @UsingShadow = (CASE WHEN @UsingShadowTable = 'True' THEN 1 ELSE 0 END);
		END
		
		-- CREATE
		EXEC [todoc].[sspCreateSaveSP]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCSSP = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	SET @ErrorSection = 'Delete Trigger';

	IF (@HasDeleteTrigger = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropDeleteTrigger]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;

		-- CREATE
		EXEC [todoc].[sspCreateDeleteTrigger]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCDT = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	SET @ErrorSection = 'Update Trigger';

	IF (@HasUpdateTrigger = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropUpdateTrigger]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
		
		-- CREATE
		EXEC [todoc].[sspCreateUpdateTrigger]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCUT = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	SET @ErrorSection = 'Insert Trigger';

	IF (@HasInsertTrigger = 'True')
	BEGIN

		-- DROP
		EXEC [todoc].[sspDropInsertTrigger]
			@TableSchema = @TableSchema, 
			@TableName = @TableName,
			@Debug = 0;
		
		-- CREATE
		EXEC [todoc].[sspCreateInsertTrigger]
			@TableSchema = @TableSchema,
			@TableName = @TableName,
			@TableColumnsCIT = @TableColumns,
			@UsingShadow = @UsingShadow,
			@Debug = 0;

	END

	-- flag the table as having todo run via the extended property
	MERGE INTO ExtProps.Properties AS target
	USING
	(
	SELECT	@TableSchema,
			@TableName,
			NULL,
			N'Todoc: Run',
			'True'
	) AS source([object_schema], [object_name], [column_name], [name], [value])
    ON 
	(
	target.[object_schema] = source.[object_schema]
	AND target.[object_name] = source.[object_name]
	AND target.[name] = source.[name]
	)
    WHEN MATCHED THEN 
        UPDATE SET [value] = source.[value]
	WHEN NOT MATCHED THEN	
	    INSERT ([object_schema], [object_name], [column_name], [name], [value])
	    VALUES (source.[object_schema], source.[object_name], source.[column_name], source.[name], source.[value]);

	COMMIT TRANSACTION;

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = @ErrorSection + '_' + @TableSchema + '_' + @TableName + ': ' + ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	ROLLBACK TRANSACTION;

	RETURN -1;

END CATCH;